<?php
	require_once PATH_OBJECT.'dataObject.php';
	
class dataObject_evenement extends dataObject
{
 	/* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_IDENTIFIANT_EVENT  = 0;
    
	/* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_TYPE_EVENT  = '';
	/* @desc    Identifiant de l'evenement
     * @var     enum('G', 'S', 'F')
     */
    public $_SOUS_TYPE_EVENT  = '';
	/* @desc    Identifiant de l'evenement
     * @var     char
     */
    public $_MOTIF_EVENT  = '';
	/* @desc    Identifiant de l'evenement
     * @var     varchar
     */
    public $_REFERENCE_EVENT  = '';
    
	/* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_DATE_EVENT  = '';
    
    /* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_IDENTIFIANT_AGENT   = 0;
    
	/* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_IDENTIFIANT_SINISTRE   = 0;
    
	/* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_IDENTIFIANT_CONTRAT   = 0;
    
	/* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_IDENTIFIANT_PERSONNE   = 0;
    
	/* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_IDENTIFIANT_ECHEANCE   = 0;
    
	/* @desc    Identifiant de l'evenement
     * @var     int
     */
    public $_IDENTIFIANT_PRESTATION   = 0;
    
    const CODE_EVENT_SOUSCRIPTION = 'ASO';
    const CODE_EVENT_RENOUVELLEMENT = 'AREC';
    const CODE_EVENT_CHANGEMENT_COUVERTURE = 'ACC';
    const CODE_EVENT_MOD_CONTRACTUEL = 'AMC';
    const CODE_EVENT_MOD_ADMINISTRATIVE = 'AMA';
    const CODE_EVENT_RESILIATION_CONTRAT = 'ARE';
    
  public function __construct( $iId=0 )
    {
       parent::__construct();

       if ( $iId != 0 )
       {
            $sQuery = "SELECT   *   /*+ dataObject_evenement::__construct */  
                       FROM evenement 
            		   WHERE Identifiant_evenement = ".$iId;
            
            $aRows =  self::$_oConnexion->query($sQuery);
	  		$aRow = $aRows[0];
            $this->_IDENTIFIANT_EVENT   = $iId;
			$this->_TYPE_EVENT 			= $aRow['Type_evenement'];
			$this->_SOUS_TYPE_EVENT 	= $aRow['Sous_type_evenement'];
			$this->_MOTIF_EVENT 		= $aRow['Motif_evenement'];
			$this->_REFERENCE_EVENT 	= $aRow['Reference_evenement'];
			$this->_DATE_EVENT 			= $aRow['Date_evenement'];
			$this->_IDENTIFIANT_AGENT 	= $aRow['Identifiant_agent'];
			$this->_IDENTIFIANT_SINISTRE 	= $aRow['Identifiant_sinistre'];
			$this->_IDENTIFIANT_CONTRAT 	= $aRow['Identifiant_contrat'];
			$this->_IDENTIFIANT_PERSONNE 	= $aRow['Identifiant_personne'];
			$this->_IDENTIFIANT_ECHEANCE 	= $aRow['Identifiant_echeance'];
			$this->_IDENTIFIANT_PRESTATION 	= $aRow['Identifiant_prestation'];		
   
       }
    }
    /**
     * récupere la table xxxx selon le(s) criter(s) précisés ou pas 
	 */
    function evenement_select( $sCritere = null )
    {
    	$sQuery = "SELECT   *    
                   FROM evenement 
            	   WHERE 1=1 
            	   {$sCritere}";
        $aRow =  self::$_oConnexion->query($sQuery);

        return $aRow;
    }
       
	
	public function evenement_insert()
    {
    	$bId = false;
    	$sQuery ="INSERT INTO evenement (
    			Type_evenement, 
				Sous_type_evenement, 
				Motif_evenement, 
				Reference_evenement, 
				Date_evenement, 
				Identifiant_agent, 
				Identifiant_sinistre, 
				Identifiant_contrat, 
				Identifiant_personne, 
				Identifiant_echeance, 
				Identifiant_prestation
    			) values (
    		'".$this->_TYPE_EVENT."', 
			'".$this->_SOUS_TYPE_EVENT."', 
			".self::$_oConnexion->escape_null($this->_MOTIF_EVENT).",
			".self::$_oConnexion->escape_null($this->_REFERENCE_EVENT).", 
			'".$this->_DATE_EVENT."',
			'".$this->_IDENTIFIANT_AGENT."',
			".self::$_oConnexion->escape_null($this->_IDENTIFIANT_SINISTRE).",
			'".$this->_IDENTIFIANT_CONTRAT."',
			".self::$_oConnexion->escape_null($this->_IDENTIFIANT_PERSONNE).",
			".self::$_oConnexion->escape_null($this->_IDENTIFIANT_ECHEANCE).",
			".self::$_oConnexion->escape_null($this->_IDENTIFIANT_PRESTATION)."
    			)";
        
        // echo $sQuery ;
    	
        	$bId = self::$_oConnexion->execute($sQuery);
    	return $bId;
    }
       
	/**
	* Insertion d'un evenement de type AMA
	*/
	public function evenement_insertAMA($_iContratId)
    {

		$bId   = FALSE ;
		$oUser = dataObject_authentification::getCurrentUser() ;

		if ($oUser)
		{
			$this->_IDENTIFIANT_AGENT = $oUser->Identifiant_agent ;
			$this->_TYPE_EVENT = 'G' ;
			$this->_SOUS_TYPE_EVENT = self::CODE_EVENT_MOD_ADMINISTRATIVE ;
            $this->_IDENTIFIANT_CONTRAT = $_iContratId ;
            $this->_DATE_EVENT = date('Y-m-d') ;
			$bId = $this->evenement_insert() ;
		}
    	return $bId ;
    }


    /**
     * liste d'Evénement du contrat
     *
     * @param int $sCritere: identifiant contrat
     * @return array
     */public function evenement_getListEvenementContrat($sCritere)

    {
    	$aRow = false;
    	$sQuery = "SELECT  *    
                   FROM evenement 
            	   WHERE Identifiant_contrat='".$sCritere."'"  ;
    	
        $aRow =  self::$_oConnexion->query($sQuery);

        return $aRow;
    }
    
    
    //elements statiques:Eléments de production

 public function evenement_getStatNiveauProduction($sDateDebut,$sDateFin)

    {

    	$sQuery ="SELECT DISTINCT DATE_FORMAT(MAX(Date_evenement),'%Y') as ANNEE,
    				DATE_FORMAT(MAX(Date_evenement), '%m') as MOIS,
    				DATE_FORMAT(MAX(Date_evenement), '%u') as SEMAINE,
    				Code_region as 'CODE REGION', Nom_region as 'LIBELLE REGION',
    				Code_unite as 'CODE UNITE', Nom_unite as 'LIBELLE UNITE',
    				IF(e.Identifiant_agent, Code_agent, '') as 'CODE AGENT', 
    				IF(e.Identifiant_agent, Nom_agent, '')  as 'NOM AGENT',
    				Numero_contrat as 'NUMERO CONTRAT',
    				DATE_FORMAT(Date_effet_contrat,'%d-%m-%Y') as 'DATE EFFET CONTRAT',
    				Libelle_offre as OFFRE,
    				Libelle_produit as PRODUIT, 
    				Libelle_formule as FORMULE,
    				(Montant_base_cotisation + Montant_frais_echeance_cotisation) as 'MONTANT COTISATION',
    				Montant_droits_entree_cotisation as 'MONTANT DROIT ENTREE', 
    				(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'M0007' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = Frequence_paiement) as 'FREQUENCE REGLEMENT', 
					(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'M0008' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = Sous_type_evenement ) as 'SOUS TYPE EVENEMENT',
					null as ' '
    			 FROM evenement e, region r, unite u, agent a, contrat c, offre o, produit p, formule f, cotisation cot,
    			 	  garantiesouscrite garsous , garantie gar, utilisateur user
    			 WHERE 
    			 	 e.Identifiant_contrat = c.Identifiant_contrat 
    			 	 AND c.Date_effet_contrat >= '".conversionDates($sDateDebut)."'
                  	 AND c.Date_effet_contrat <= '".conversionDates($sDateFin)."'	
    				 AND ( ( e.Identifiant_agent is NULL
       						AND c.Identifiant_unite = u.Identifiant_unite 
      						)
    					 OR ( e.Identifiant_agent = user.Identifiant_utilisateur 
							AND user.Code_utilisateur = a.Code_agent 
							AND a.Identifiant_unite = u.Identifiant_unite 
							)
  						 )
     				 AND u.Identifiant_region = r.Identifiant_region
    				 AND c.Identifiant_contrat = cot.Identifiant_contrat
    				 AND c.Identifiant_contrat = garsous.Identifiant_contrat
    			  	 AND garsous.Identifiant_formule = f.Identifiant_formule
    				 AND garsous.Identifiant_garantie = gar.Identifiant_garantie
    			 	 AND gar.Identifiant_produit = p.Identifiant_produit
    				 AND p.Identifiant_offre = o.Identifiant_offre
    				 AND ( Sous_type_evenement IN ('".self::CODE_EVENT_SOUSCRIPTION."','".
    											    self::CODE_EVENT_RENOUVELLEMENT."','".
    											    self::CODE_EVENT_CHANGEMENT_COUVERTURE."','".
    											    self::CODE_EVENT_MOD_CONTRACTUEL."') 
    						OR Motif_evenement = 'CC' )
    				group by Numero_contrat
    				order by ANNEE, MOIS, SEMAINE, 'NUMERO CONTRAT'"  ;	 

        $aRows =  self::$_oConnexion->query($sQuery);
        
        return $aRows;
    }
    
    
    //elements statiques: Eléments de sinistralité
    
    public function evenement_getStatNiveauSinistre($sDateDebut,$sDateFin)

    {
		 $sQuery = "SELECT DISTINCT DATE_FORMAT(Date_declaration_sinistre,'%Y') as ANNEE,
    				DATE_FORMAT(Date_declaration_sinistre, '%m') as MOIS,
    				DATE_FORMAT(Date_declaration_sinistre, '%u') as SEMAINE,
    				Code_region as 'CODE REGION',
    				Nom_region as 'LIBELLE REGION',
    				Code_unite as 'CODE UNITE',
    				Nom_unite  as 'LIBELLE UNITE',
    				Code_agent as 'CODE AGENT',
    				Nom_agent as 'NOM AGENT',
    				Numero_contrat as 'NUMERO CONTRAT',
    				Libelle_offre as OFFRE,
    				Libelle_produit as PRODUIT, 
    				Libelle_formule as FORMULE,
    				(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'M0011' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = Type_sinistre) as 'TYPE SINISTRE',
    				DATE_FORMAT(Date_survenance_sinistre,'%d-%m-%Y') as 'DATE SURVENANCE', 
    				DATE_FORMAT(Date_declaration_sinistre,'%d-%m-%Y') as 'DATE DECLARATION',
    				(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'T0014' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = Etat_sinistre) as 'ETAT SINISTRE',
    				(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'T0013' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = Decision_partenaire_sinistre) as 'DECISION ASSUREUR',
    				(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'M0013' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = Motif_refus_sinistre) as 'MOTIF REFUS',
    				(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'M0014' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = CONCAT('N',Type_prestation)) as 'TYPE PRESTATION',
    				Montant_prestation as 'MONTANT PRESTATION',
    				DATE_FORMAT(ev.Date_evenement,'%d-%m-%Y') as 'DATE REGLEMENT PRESTATION', null as ' '
    				
    			 FROM  sinistres s
    			 	  INNER JOIN evenement e ON ( e.Identifiant_sinistre = s.Identifiant_sinistre AND Sous_type_evenement = 'SD')
    			      INNER JOIN contrat c ON s.Identifiant_contrat = c.Identifiant_contrat
    			      INNER JOIN utilisateur user ON e.Identifiant_agent = user.Identifiant_utilisateur
    			      INNER JOIN agent a ON (e.Identifiant_agent = user.Identifiant_utilisateur AND user.Code_utilisateur = a.Code_agent)
    			      INNER JOIN unite u ON a.Identifiant_unite = u.Identifiant_unite
    			      INNER JOIN region r ON u.Identifiant_region = r.Identifiant_region
    			      INNER JOIN garantiesouscrite garsous ON c.Identifiant_contrat = garsous.Identifiant_contrat
    			      INNER JOIN formule f ON garsous.Identifiant_formule = f.Identifiant_formule
    			      INNER JOIN garantie gar ON garsous.Identifiant_garantie = gar.Identifiant_garantie
    			      INNER JOIN produit p ON gar.Identifiant_produit = p.Identifiant_produit
    			      INNER JOIN offre o ON p.Identifiant_offre = o.Identifiant_offre
    			      LEFT JOIN  prestation pre ON s.Identifiant_sinistre = pre.Identifiant_sinistre
    			      LEFT JOIN evenement ev ON (ev.Identifiant_sinistre = s.Identifiant_sinistre AND ev.Identifiant_prestation = pre.Identifiant_prestation )
    			 WHERE s.Date_declaration_sinistre >= '".conversionDates($sDateDebut)."'
                  	 AND s.Date_declaration_sinistre <= '".conversionDates($sDateFin)."' ";
	
        $aRows =  self::$_oConnexion->query($sQuery);
        
        return $aRows;
    }
    
    
//elements statiques: Eléments de niveau d’encaissement
    
    public function evenement_getStatNiveauEncaissement($sDateDebut,$sDateFin)

    {
		 $sQuery = "SELECT DISTINCT DATE_FORMAT(ec.Date_debut_echeance,'%Y') as ANNEE,
    				DATE_FORMAT(ec.Date_debut_echeance, '%m') as MOIS,
    				Code_region as 'CODE REGION',
    				Nom_region as 'LIBELLE REGION',
    				Code_unite as 'CODE UNITE',
    				Nom_unite  as 'LIBELLE UNITE',
    				Code_agent as 'CODE AGENT',
    				Nom_agent as 'NOM AGENT',
    				Numero_contrat as 'NUMERO CONTRAT',
    				Libelle_offre as OFFRE,
    				Libelle_produit as PRODUIT, 
    				Libelle_formule as FORMULE,
    				(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'M0007' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = Frequence_paiement) as 'FREQUENCE REGLEMENT',
    				DATE_FORMAT(Date_debut_echeance,'%d-%m-%Y') as 'DATE DEBUT ECHEANCE',
    				DATE_FORMAT(Date_fin_echeance,'%d-%m-%Y') as 'DATE FIN ECHEANCE',
    				(select Nom_element from dictionnaire,elementdico WHERE dictionnaire.Code_table = 'T0010' AND dictionnaire.Identifiant_table = elementdico.Identifiant_table AND Code_element = Etat_echeance) as 'ETAT ECHEANCE',
    				Montant_echeance as 'MONTANT ECHEANCE',
    				if(strcmp(Etat_echeance,'R')=0, DATE_FORMAT(Date_evenement,'%d-%m-%Y'), '') as 'DATE REGLEMENT ECHEANCE', null as ' '
    				
    			 FROM echeancier ec, evenement e, contrat c,  cotisation cot , region r, unite u, agent a,
    			 offre o, produit p, formule f, garantiesouscrite garsous, garantie gar, utilisateur user
    			      
    			 WHERE ec.Date_debut_echeance >= '".conversionDates($sDateDebut)."'
                 	 AND ec.Date_fin_echeance <= '".conversionDates($sDateFin)."'	
    			 	 AND (e.Identifiant_echeance = ec.Identifiant_echeance 
    			 	      OR (e.Identifiant_echeance is NULL and Sous_type_evenement ='ASO') )
    			 	 AND e.Identifiant_contrat = c.Identifiant_contrat
    			 	 AND ec.Identifiant_cotisation = cot.Identifiant_cotisation
                 	 AND cot.Identifiant_contrat = c.Identifiant_contrat 
                 	 AND e.Identifiant_agent = user.Identifiant_utilisateur
                 	 AND user.Code_utilisateur = a.Code_agent
    				 AND a.Identifiant_unite = u.Identifiant_unite
    				 AND u.Identifiant_region = r.Identifiant_region
    				 AND c.Identifiant_contrat = garsous.Identifiant_contrat
    			  	 AND garsous.Identifiant_formule = f.Identifiant_formule
    				 AND garsous.Identifiant_garantie = gar.Identifiant_garantie
    			 	 AND gar.Identifiant_produit = p.Identifiant_produit
    				 AND p.Identifiant_offre = o.Identifiant_offre ";	
		 
        $aRows =  self::$_oConnexion->query($sQuery);
        
        return $aRows;
    }
    

	/*
     *  Créer évènement relatif à l'échéance
     */
    public function evenement_creerEcheance()
    {

    	$sQuery = "INSERT INTO evenement (Type_evenement, Sous_type_evenement, Reference_evenement, Date_evenement, Identifiant_agent, Identifiant_contrat, Identifiant_personne, Identifiant_echeance)
    			   values (
    			'F',
    			'FRE',
				'".$this->_REFERENCE_EVENT."', 
				'".$this->_DATE_EVENT."', 
    			$this->_IDENTIFIANT_AGENT, 
    			$this->_IDENTIFIANT_CONTRAT, 
    			".self::$_oConnexion->escape_null($this->_IDENTIFIANT_PERSONNE).",
    			$this->_IDENTIFIANT_ECHEANCE
    			)";
    	
        	$bId = self::$_oConnexion->execute($sQuery);
    	return $bId;
    }

    
}
?>